#!/usr/bin/env python
#-*- coding=utf-8 -*-

import re
import threading
import getopt,sys,os
import MySQLdb
import time

#Global configure variables
mysql_host      = "127.0.0.1"
mysql_user      = "root"
mysql_socket    = "/tmp/mysql.sock"
max_threads     = 10
cur_threads     = threading.BoundedSemaphore(value=max_threads)
threads         = {}
log_tmp_fd      = {}    
cur             = {}
conn             = {}
autocommit      = True
remove_tmp      = True
execute         = True
log_parse_ing   = True
log_file_fd     = file.__new__
log_tmp_path    = "tmp"

def usage():
    print """
Usage:
 ./log_parse.py [ options ] mysql.log 
 Options:   
    -h,--help           Display this message
    --autocommit=[0|1]  set autocommit value for session, 
                        0 by default.
    --remove-tmp=[0|1]  If this option's value is 1 program will remove tmp file automatically, 
                        1 by default
    --execute=[0|1]  If this option's value is 0 program will not replay mysql log, just parse them and output temp file.
                        this parameter will by pass the --remove-tmp option
                        1 by default
    """

def env_init():
    global log_tmp_path
    log_tmp_path = os.getcwd()+"/"+log_tmp_path 
    #os.system("rm -rf "+log_tmp_path)
    if not os.path.isdir(log_tmp_path):
        os.mkdir(log_tmp_path) 
    else:
        print "temp file exists script will replay them!!."

def get_options():
    global autocommit,remove_tmp,execute,log_file_fd

    try:
        opts,argv = getopt.getopt(sys.argv[1:],'h',["help","remove-tmp=","autocommit=","execute="])
        log_file_name=argv[0];
    except (getopt.GetoptError,IndexError),err:
        print str(err)
        usage()
        sys.exit(2)

    for opt,arg in opts:
        if opt in ("-h", "--help"):
            usage()
            sys.exit(0)
        if opt == "--autocommit":
            autocommit = bool(int(arg))
        if opt == "--remove-tmp":
            remove_tmp = bool(int(arg))
        if opt == "--execute":
            execute = bool(int(arg))

    if os.path.isfile(log_file_name):
        log_file_fd =  open(log_file_name,'r');
        return log_file_fd
    else:
        print 'File : "'+log_file_name+'" doesn\'t exist!'
        sys.exit(2)

def log_err_write(err_msg):
    err_log_fd = open("err_log","a+")
    err_log_fd.write(err_msg)
    err_log_fd.close()

def mysql_log_readline(log_file_fd):
    thd={"thread_id":"","command":"","query":""}

    log_line = log_file_fd.readline();

    file_header_regex = re.compile("((^Tcp port: \d+)|(.*started with:\n$)|(Time                 Id Command    Argument\n$))")

    while file_header_regex.match(log_line):
        log_line = log_file_fd.readline();

    line_header_regex = re.compile("((^[0-9]{6} [0-9]{2}:[0-9]{2}:[0-9]{2}\s+)|(^\s+))(\d+) (.{11})[ ]?")
    query = line_header_regex.sub('',log_line)


    if query != log_line:
        thd_info = line_header_regex.split(log_line)
        thd['thread_id'] = thd_info[4]
        thd['command']   = thd_info[5].strip()

    last_line = log_file_fd.tell()
    log_line  = log_file_fd.readline();
    while log_line:
        if line_header_regex.match(log_line):
            log_file_fd.seek(last_line)
            break;
        elif file_header_regex.match(log_line):
            log_line = log_file_fd.readline();
        else:
            query=query.strip()+" "+log_line
            last_line = log_file_fd.tell()
            log_line = log_file_fd.readline();
    
    thd['query'] = query
    tmp_log_fd   = open('tmp/'+thd['thread_id']+'.tmp','a+');
    tmp_log_fd.write(parse_query(thd));
    tmp_log_fd.close();
    return thd

def mysql_connect(thd):
    global autocommit,conn
    try:
        conn[thd['thread_id']] = MySQLdb.connect(mysql_host,mysql_user,port=5051,connect_timeout=1)
        #conn = MySQLdb.connect(mysql_host,mysql_user,unix_socket=mysql_socket,connect_timeout=1)
        #注意MySQLdb.connect连接后会带一个set autocommit=0
        cur  = conn[thd['thread_id']].cursor()
        if autocommit:
            cur.execute("set autocommit=1")
    except Exception, err:
        print err
        sys.exit()
    return cur


def execute_query(cur,query):
    try:
        cur.execute(query)
    except Exception, err:
        #print "######\n",query, err
        err_msg = "######\n"+query+err[1]
        log_err_write(err_msg)
    #sys.stdout.write(query)

def dispatch_command(thd):
    global log_tmp_fd,cur,cur_threads,log_file_fd
    try:
        log_tmp_fd[thd['thread_id']]
    except:
        log_tmp_fd[thd['thread_id']]  = open("tmp/"+thd['thread_id']+".tmp",'r')
        try:
            cur[thd['thread_id']]
        except:
            cur[thd['thread_id']] = mysql_connect(thd)
    while True and (not log_tmp_fd[thd['thread_id']].closed):
        line  = log_tmp_fd[thd['thread_id']].readline()
        if line == "Quit\n": #如果tmp文件不以Quit结尾该线程将一直等待
            conn[thd['thread_id']].close()
            threads.pop(thd['thread_id'])
            if remove_tmp:
                os.remove(log_tmp_path+"/"+thd['thread_id']+".tmp")
            log_tmp_fd[thd['thread_id']].close()
            break;
        if line:
            execute_query(cur[thd['thread_id']],line)
        elif log_file_fd.closed:
            conn[thd['thread_id']].close()
            break;
    cur_threads.release()

def handle_one_thread(thd):
    global threads,cur_threads
    cur_threads.acquire()
    threads[thd['thread_id']] = threading.Thread(target=dispatch_command,args=(thd,))
    threads[thd['thread_id']].start()
    #cur_threads.release() # should release at the end of dispatch_command

def parse_query(thd):
    query=""
    # Command list switch case
    #if thd['command'] == "Sleep":
    #    pass
    if thd['command'] == "Quit":
        query = "Quit\n"
    if thd['command'] == "Init DB":
        query = "USE "+thd['query']
    if thd['command'] == "Query":
        query = thd['query']
    #if thd['command'] == "Field List":
    #    query = "DESC "+thd['query']
    #if thd['command'] == "Create DB":
    #    pass
    #if thd['command'] == "Drop DB":
    #    pass
    if thd['command'] == "Refresh":
        pass
    if thd['command'] == "Shutdown":
        pass
    if thd['command'] == "Statistics":
        pass
    #if thd['command'] == "Processlist":
    #    pass
    if thd['command'] == "Connect":
        user_host_regex = re.compile("^\w+@.+ on ")
        db = user_host_regex.sub('',thd['query'])
        if db != "\n":
            query = "USE "+db
    #if thd['command'] == "Kill":
    #    pass
    #if thd['command'] == "Debug":
    #    pass
    #if thd['command'] == "Ping":
    #    pass
    #if thd['command'] == "Time":
    #    pass
    #if thd['command'] == "Delayed insert":
    #    pass
    #if thd['command'] == "Change user":
    #    pass
    #if thd['command'] == "Binlog Dump":
    #    pass
    #if thd['command'] == "Table Dump":
    #    pass
    #if thd['command'] == "Connect Out":
    #    pass
    #if thd['command'] == "Register Slave":
    #    pass
    #if thd['command'] == "Prepare":
    #    pass
    #if thd['command'] == "Execute":
    #    pass
    #if thd['command'] == "Long Data":
    #    pass
    #if thd['command'] == "Close stmt":
    #    pass
    #if thd['command'] == "Reset stmt":
    #    pass
    #if thd['command'] == "Set option":
    #    pass
    #if thd['command'] == "Fetch":
    #    pass
    #if thd['command'] == "Error":
    #    pass
    return query


def main():
    env_init();
    global log_file_fd

    log_file_fd = get_options()

    thd=mysql_log_readline(log_file_fd)
    while thd['query']:
        if (thd['thread_id'] != '') and execute: #如果thread_id为空表明该行为log的头
            try:
                threads[thd['thread_id']]
            except:
                handle_one_thread(thd)
        thd=mysql_log_readline(log_file_fd)

    #回收资源
    log_file_fd.close()
    #if execute:
    #    threads_list    = os.listdir(log_tmp_path)
    #    for thread in threads_list:
    #        tmp_log_fd   = open('tmp/'+thread,'a+');
    #        tmp_log_fd.write('Quit\n'); #避免dispatch_command线程一直等待将tmp文件末尾追加Quit提示
    #        tmp_log_fd.close();

if __name__ == '__main__':
    main()
